
clear all
set more off

** Change path to re-run on another computer
cd ""
**



*$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
*1) Figure 6
*$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

* Load and clean price and tax data
*************************
insheet using "IEAEnergypriceindex/update2016_end_toe_nat_curr_Industry.csv", nodouble comma clear

rename time year

keep location country v4 v8 year value

keep if v8=="Total price (nat. cur./toe NCV)" | v8=="Price excluding taxes (nat. cur./toe NCV)"

gen z1=lower(v4)
replace z1 = subinstr(z1," ","",.)
drop v4

reshape wide value, i(location country year v8) j(z1) string
rename value* *

drop if year>=2016

drop location
fillin country year v8
drop _fill

rename v8 flow
tab flow

*need to add the non-OECD again:
preserve
insheet using "IEAEnergypriceindex/end_toe_taxes_and_prices.csv", nodouble comma clear
rename time year
drop if year==.
destring high-liq, replace force float
compress
replace flow="Price excluding taxes (nat. cur./toe NCV)" if flow=="Price (excluding tax) in nat. cur./toe (NCV)"
replace flow="Total price (nat. cur./toe NCV)" if flow=="Total price in nat. cur./toe (NCV)"
tempfile oldend
save `oldend'
restore

gen  new=1
merge 1:1 country year flow using `oldend', update
tab year if _m==1
drop if _m==5 & new!=1
drop _m

rename (highsulphurfueloil lowsulphur lightfueloil naturalgas steamcoal cokingcoal electricity liquef) (oil_hs oil_ls oil_light gas coal_steam coal_coke electricity lpgas)

gen type="_net_price"
replace type="_gross_price" if flow=="Price (excluding tax) in nat. cur./toe (NCV)" | flow=="Price excluding taxes (nat. cur./toe NCV)"
tab flow
isid cou year type
drop flow new
reshape wide oil_hs oil_ls oil_light gas coal_steam coal_coke electricity lpgas, i(country year) j(type) string

local fuel "oil_hs oil_ls oil_light gas coal_steam coal_coke electricity lpgas"

foreach x of local fuel{
gen price_`x'=`x'_net_price
gen tax_`x'=`x'_net_price-`x'_gross_price
drop `x'_net_price
}



*MERGE with deflator and exchange rate and DEFLATE
*************************

replace country="Korea, Republic of" if country=="Korea"
replace country="Slovakia" if country=="Slovak Republic"
replace country="United States of America" if country=="United States"
replace country="Venezuela (Bolivarian Republic of)" if country=="Venezuela"
replace country="China" if country=="People's Republic of China"
replace country="Taiwan, Republic of China" if country=="Chinese Taipei"

merge m:1 country using "Country Isocodes/isocodes_country"

drop if _m==2  //some exotic non oecd countries

drop _m 

order iso*

merge m:1 isoalpha3code year using "Other data/GDP deflator/GDP_deflator"

drop if _m==2 // countries that not needed
drop _m

merge m:1 isoalpha3code year using "Other data/Exchange rates/exchange_rates"
drop if _m==2 // countries that not needed
drop _m


*Make deflator consistent base year 2010

xtset isonum year, yearly

gen growth_defl=(deflator-L.deflator)/L.deflator
gen deflator2010=1 if year==2010
order growth_defl deflator2010, after(deflator)

forvalues i = 1/5 {
	replace deflator2010=L.deflator2010*(1+growth_defl) if deflator2010==.
}
forvalues i = 1/40 {
	replace deflator2010=F.deflator2010/(1+F.growth_defl) if deflator2010==.
}
drop growth_defl

label var deflator2010 "GDP deflator (consistent base year 2010)"



*Deflate:

gen temp = deflator2010/exchange_rate if year == 2010
bysort country: egen scalar=total(temp)

local fuel "oil_hs oil_ls oil_light gas coal_steam coal_coke electricity lpgas"

foreach f of local fuel {
gen price_`f'_NCU_real=price_`f'/deflator2010
gen pricegross_`f'_NCU_real=`f'_gross_price/deflator2010
gen tax_`f'_NCU_real=tax_`f'/deflator2010

gen price_`f'_consUS=price_`f'_NCU_real*scalar
gen pricegross_`f'_consUS=pricegross_`f'_NCU_real*scalar
gen tax_`f'_consUS=tax_`f'_NCU_real*scalar

order price_`f'_consUS pricegross_`f'_consUS tax_`f'_consUS price_`f'_NCU_real pricegross_`f'_NCU_real tax_`f'_NCU_real, after(price_`f')
}

drop scalar temp
sort country year

keep isoalpha3code isonum country year deflator2010 exchange_rate *consUS

******** REPLACE zeros with missings ***********
tab tax_electricity_consUS //half of it is zero

local fuel "oil_hs oil_ls oil_light gas coal_steam coal_coke electricity lpgas"

foreach f of local fuel {
replace tax_`f'_consUS=. if tax_`f'_consUS==0
}
*************

*Generate graph
*************************

local fuel "oil_hs   gas coal_steam  electricity "
foreach f of local fuel{
gen r2_`f'=.
gen r2_sig_`f'=.
gen r2_obs_`f'=.
forvalues i = 1978/2015{
capture noisily regress price_`f'_consUS tax_`f'_consUS if year==`i'
capture noisily replace r2_`f'=e(r2) if year==`i'
capture noisily replace r2_sig_`f'=2*ttail(e(df_r),abs(_b[tax_`f'_consUS]/_se[tax_`f'_consUS])) if year==`i'
capture noisily replace r2_obs_`f'=e(N) if year==`i'
}
}

keep if year>1994

local fuel "oil_hs   gas coal_steam  electricity "
foreach f of local fuel{
gen sig_r2_`f'= r2_`f' if r2_sig_`f'<=0.05
gen notsig_r2_`f'= r2_`f' if r2_sig_`f'>0.05
}

keep year sig_r2_oil_hs notsig_r2_oil_hs sig_r2_gas notsig_r2_gas sig_r2_coal_steam notsig_r2_coal_steam sig_r2_electricity notsig_r2_electricity      r2_obs_coal_steam r2_obs_electricity r2_obs_oil_hs r2_obs_gas
duplicates drop

reshape long sig_r2_ notsig_r2_ r2_obs_, i(year) j(fuel) string
replace fuel ="Oil products" if fuel== "oil_hs"
replace fuel ="Coal" if fuel== "coal_steam"
replace fuel ="Natural Gas" if fuel== "gas"
replace fuel ="Electricity" if fuel== "electricity"

rename sig_r2_ R2_significant

rename notsig_ R2_not_significant

scatter R2_significant R2_not_significant year, /*
*/  by(fuel,graphregion(color(white)) note("")) ytitle("R-squared of total price on tax regression", height(10))  ylabel(#9, angle(horizontal)) yscale(r(0 1)) legend(rows(1) label(1 "R-squared and significant") label(2 "R-squared and insignificant")) xlabel(#5, angle(horizontal)) msymbol(o oh) xtitle("Year") 
graph export "Graphs/some descriptive statistics/correlation tax and price/scatter_r2_price_tax/all fuels scatter.pdf", replace

